package com.wy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wy.entity.GoodsDeposit;
import com.wy.entity.GoodsEvaluation;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author 王渝
 */
@Mapper
public interface GoodsEvaluationMapper extends BaseMapper<GoodsEvaluation> {


    /**
     * 根据用商品id查询用户平均评分
     * @param goodsId 用户id
     * @return
     */
    @Select("SELECT avg(rate) avg_rate\n" +
            "FROM goods_evaluation\n" +
            "WHERE seller_user_id =  (SELECT user_id FROM goods_info WHERE goods_id = #{goodsId})")
    BigDecimal getAvgRateBySellerGoodsId(Long goodsId);

    /**
     * 根据用用户id查询他的平均评分
     * @param userId 用户id
     * @return
     */
    @Select("SELECT avg(rate) avg_rate\n" +
            "FROM goods_evaluation\n" +
            "WHERE seller_user_id =  #{userId}")
    BigDecimal getAvgRateBySellerUserId(Long userId);

    /**
     *
     * 根据卖方id查询全部评价
     * @param sellerUserId
     * @return
     */
    @Select("SELECT goods_evaluation.* ,user_info.head_image,user_info.user_name\n" +
            "FROM goods_evaluation,user_info\n" +
            "WHERE seller_user_id = #{sellerUserId}\n" +
            "AND user_info.user_id = goods_evaluation.buyer_user_id")
    List<GoodsEvaluation> getAllBySellerUserId(Long sellerUserId);


    /**
     * 查询全部评价（我评价的）
     * @param buyUserId 买方id
     * @return
     */
    @Select("SELECT goods_name,goods_info.goods_id ,rate,goods_evaluation.detail,goods_evaluation.create_time,seller_user_id,buyer_user_id,evaluation_id\n" +
            "FROM goods_info,goods_evaluation\n" +
            "WHERE goods_info.goods_id = goods_evaluation.goods_id\n" +
            "AND buyer_user_id = #{buyUserId}")
    List<GoodsEvaluation> getAllByBuyUserId(Long buyUserId);


    /**
     * 查询全部评价（回复我的）
     * @param sellerUserId 卖方id
     * @return
     */
    @Select("SELECT goods_name,goods_info.goods_id ,rate,goods_evaluation.detail,goods_evaluation.create_time,seller_user_id,buyer_user_id,evaluation_id\n" +
            "FROM goods_info,goods_evaluation\n" +
            "WHERE goods_info.goods_id = goods_evaluation.goods_id\n" +
            "AND seller_user_id = #{sellerUserId}")
    List<GoodsEvaluation> getAllByBuyUserId2(Long sellerUserId);


    /**
     * 管理员查询全部评论
     * @return
     */
    @Select("SELECT goods_evaluation.* ,seller.user_name seller_name,buyer.user_name buyer_name\n" +
            "FROM goods_evaluation," +
            "(SELECT user_name ,user_id FROM user_info,goods_evaluation WHERE seller_user_id = user_id) seller,\n" +
            "(SELECT user_name ,user_id FROM user_info,goods_evaluation WHERE buyer_user_id = user_id) buyer\n" +
            "WHERE buyer.user_id = buyer_user_id " +
            "AND seller_user_id = seller.user_id\n" +
            "GROUP BY evaluation_id")
    List<GoodsEvaluation> queryAllByAdmin();
}